
package com.hex.ds.code.detection.pack.service.impl;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.entity.TemplateExportParams;
import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import cn.hutool.core.util.ZipUtil;
import com.alibaba.fastjson.JSON;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.baomidou.mybatisplus.core.toolkit.Wrappers;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import com.hex.ds.code.detection.common.entity.ScriptPackScanResultEntity;
import com.hex.ds.code.detection.pack.dao.ScriptPackScanResultMapper;
import com.hex.ds.code.detection.pack.entity.ScriptPackScanListEntity;
import com.hex.ds.code.detection.pack.service.IScriptPackScanResultService;
import com.hex.ds.code.detection.common.base.PageResult;
import com.hex.ds.code.detection.pack.entity.SummaryScanResultEntity;
import lombok.RequiredArgsConstructor;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.context.annotation.Lazy;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.util.*;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * 脚本扫描结果
 *
 * @author Wang zhihao
 * @date 2023-10-07 17:48:50
 */
@Service
@RequiredArgsConstructor(onConstructor_ = {@Lazy, @Autowired})
public class ScriptPackScanResultServiceImpl extends ServiceImpl<ScriptPackScanResultMapper, ScriptPackScanResultEntity> implements
    IScriptPackScanResultService {

    private final ScriptPackScanResultMapper scriptPackScanResultMapper;

    private final ScriptPackScanListServiceImpl scriptPackScanListService;

    @Value("${script.pack.relativePath}")
    private String relativePath;

    @Value("${script.scan.exportFilePath}")
    private String exportFilePath;

    /**
     * @Method queryList
     * @Param
     * @param page
     * @param scriptPackScanResult
     * @Return com.hex.ds.code.detection.common.base.PageResult
     * @Description 分页查询并返回PageResult类型的对象
     * @Author Wang zhihao
     * @Date 2023/10/16 13:33
     * @Version V1.0
     */
    @Override
    public PageResult queryList(Page page, ScriptPackScanResultEntity scriptPackScanResult) {
        LambdaQueryWrapper<ScriptPackScanResultEntity> wrapper = Wrappers.lambdaQuery();
        if (StrUtil.isNotBlank(scriptPackScanResult.getScanItemDescribe())) {
            wrapper.like(ScriptPackScanResultEntity::getScanItemDescribe, scriptPackScanResult.getScanItemDescribe());
        }
        wrapper.eq(StrUtil.isNotBlank(scriptPackScanResult.getScanListId()), ScriptPackScanResultEntity::getScanListId, scriptPackScanResult.getScanListId());
        wrapper.last("ORDER BY CAST(SCAN_SQL_INDEX AS INTEGER), scan_item ASC");
        Page returnPage = this.baseMapper.selectPage(page, wrapper);
        return new PageResult(returnPage.getRecords(), returnPage.getTotal());
    }

    /**
     * @Method queryListNoPage
     * @Param
     * @param scriptPackScanResult
     * @Return java.util.List<com.hex.ds.code.detection.component.code.detection.entity.ScriptPackScanResultEntity>
     * @Description 无分页查询
     * @Author Wang zhihao
     * @Date 2023/10/23 9:42
     * @Version V1.0
     */
    @Override
    public List<ScriptPackScanResultEntity> queryListNoPage(ScriptPackScanResultEntity scriptPackScanResult) {
        LambdaQueryWrapper<ScriptPackScanResultEntity> wrapper = Wrappers.lambdaQuery();
        if (StrUtil.isNotBlank(scriptPackScanResult.getScanListId())) {
            wrapper.eq(ScriptPackScanResultEntity::getScanListId, scriptPackScanResult.getScanListId());
        }
        return this.baseMapper.selectList(wrapper);
    }

    /**
     * @Method removeByListId
     * @Param
     * @param listId
     * @Return void
     * @Description 根据清单ID删除resultInfo
     * @Author Wang zhihao
     * @Date 2023/10/23 9:54
     * @Version V1.0
     */
    @Override
    public void removeByListId(String listId) {
        scriptPackScanResultMapper.removeByListId(listId);
    }

    /**
     * @Method download
     * @Param
     * @param pkIds
     * @param taskId
     * @Return java.io.File
     * @Description 导出结果清单
     * @Author Wang zhihao
     * @Date 2023/11/1 16:15
     * @Version V1.0
     */
    @Override
    public String download(List<String> pkIds, String taskId) {
        if (CollectionUtil.isEmpty(pkIds)) {
            List<ScriptPackScanListEntity> scriptPackScanListEntities = scriptPackScanListService.queryListNoPage(new ScriptPackScanListEntity().setScriptPackTaskId(taskId));
            // 全量下载校验是否存在未完成任务
            checkTaskIsDone(scriptPackScanListEntities);
            pkIds = scriptPackScanListEntities.stream().map(ScriptPackScanListEntity::getId).collect(Collectors.toList());
        }
        String exportPath = relativePath + "/pack/" + taskId + exportFilePath + "/" + DateUtil.format(new DateTime(), "yyyyMMddHHmmss");
        try {
            for (String pkId : pkIds) {
                ScriptPackScanListEntity scriptPackScanList = scriptPackScanListService.getById(pkId);
                // 获取扫描结果信息
                List<ScriptPackScanResultEntity> scriptPackScanResultEntities = queryListNoPage(new ScriptPackScanResultEntity().setScanListId(pkId));
                String fileName = "周期脚本".equals(scriptPackScanList.getFileAffiliationName()) ? scriptPackScanList.getScanFileName()
                        : scriptPackScanList.getScanFilePath().substring(scriptPackScanList.getScanFilePath().lastIndexOf('/', scriptPackScanList.getScanFilePath().lastIndexOf('/') - 1) + 1).replace("/","-");
                // 下载文件名
                String downloadFileName = scriptPackScanList.getFileAffiliationName() + fileName + DateUtil.format(new DateTime(), "yyyyMMddHHmmss") + ".xls";
                // 下载文件到服务器
                downScanResultFile(exportPath + "/", downloadFileName, scriptPackScanResultEntities);
            }
            summaryScanResult(exportPath + "/", scriptPackScanListService.queryListNoPage(new ScriptPackScanListEntity().setScriptPackTaskId(taskId)));
            // 将文件压缩
            ZipUtil.zip(exportPath, StandardCharsets.UTF_8);
        } catch (Exception e) {
            log.error("结果清单下载异常:" + e.getMessage(), e);
            throw new RuntimeException("结果清单下载异常" + e.getMessage(), e);
        }
        return exportPath + ".zip";
    }

    private void checkTaskIsDone(List<ScriptPackScanListEntity> scriptPackScanListEntities) {
        // 若存在任一个未完成的文件扫描 则抛异常
        if (scriptPackScanListEntities.stream().anyMatch(s -> StrUtil.equalsAny(s.getScanState(), "SCANNING", "UNSCAN"))) {
            log.error("存在尚未扫描完成的任务");
            throw new RuntimeException("存在尚未扫描完成的任务，请稍后下载");
        }
    }

    /**
     * @Method ignoreById
     * @Param
     * @param scriptPackScanResult
     * @Return void
     * @Description 根据ID忽略异常
     * @Author Wang zhihao
     * @Date 2023/12/19 17:32
     * @Version V1.0
     */
    @Override
    public void ignoreById(ScriptPackScanResultEntity scriptPackScanResult) {
        scriptPackScanResult.setScanState("ignore");
        scriptPackScanResultMapper.updateById(scriptPackScanResult);
    }

    /**
     * @Method ignoreAndRefreshList
     * @Param
     * @param listId
     * @param id
     * @Return void
     * @Description 根据ID忽略异常并刷新
     * @Author Wang zhihao
     * @Date 2023/12/19 18:22
     * @Version V1.0
     */
    @Override
    public void ignoreAndRefreshList(String listId, String id) {
        ScriptPackScanResultEntity scriptPackScanResult = getById(id);
        scriptPackScanResult.setScanState("ignore");
        scriptPackScanResultMapper.updateById(scriptPackScanResult);
        scriptPackScanListService.refreshStateById(listId);
    }

    /**
     * @Method downScanResultFile
     * @Param
     * @param exportPath
     * @param downloadFileName
     * @param scriptPackScanResultEntities
     * @Return java.io.File
     * @Description 下载文件到服务器
     * @Author Wang zhihao
     * @Date 2023/11/1 16:15
     * @Version V1.0
     */
    private File downScanResultFile(String exportPath, String downloadFileName, List<ScriptPackScanResultEntity> scriptPackScanResultEntities) {
        Workbook workbook = null;
        File saveFile = null;
        try {
            // 模板位置
            String templateFilePath = "static/scan_result_import_template.xls";
            // 读取模板
            final TemplateExportParams templateExportParams = new TemplateExportParams(templateFilePath);
            // 获取导出数据
            Map<Integer, List<Map<String, Object>>> sheetMap = getDownloadScanResultFileData(scriptPackScanResultEntities);
            // 生成数据
            workbook = ExcelExportUtil.exportExcelClone(sheetMap, templateExportParams);
            // 创建文件
            saveFile = createDatafile(exportPath, downloadFileName);
            // 写入数据
            writeDownloadFileData(saveFile, workbook);
        } catch (Exception e) {
            log.error("下载文件到服务器异常" + e.getMessage(), e);
            throw new RuntimeException("下载文件到服务器异常" + e.getMessage(), e);
        } finally {
            try {
                workbook.close();
            } catch (Exception e) {
                log.error("下载时关闭异常");
                e.printStackTrace();
            }
        }
        return saveFile;
    }

    /**
     * @Method summaryScanResult
     * @Param
     * @param exportPath
     * @param scriptPackScanListEntities
     * @Return java.io.File
     * @Description 导出汇总扫描清单
     * @Author Wang zhihao
     * @Date 2023/12/25 19:47
     * @Version V1.0
     */
    private File summaryScanResult(String exportPath, List<ScriptPackScanListEntity> scriptPackScanListEntities) {
        Workbook workbook = null;
        File saveFile = null;
        try {
            // 模板位置
            String templateFilePath = "static/summary_scan_result_template.xls";
            // 读取模板
            final TemplateExportParams templateExportParams = new TemplateExportParams(templateFilePath);
            // 获取导出数据
            Map<Integer, List<Map<String, Object>>> sheetMap = getDownloadScanListFileData(scriptPackScanListEntities);
            // 生成数据
            workbook = ExcelExportUtil.exportExcelClone(sheetMap, templateExportParams);
            // 创建文件
            saveFile = createDatafile(exportPath, "汇总清单.xls");
            // 写入数据
            writeDownloadFileData(saveFile, workbook);
        } catch (Exception e) {
            log.error("导出清单到服务器异常" + e.getMessage(), e);
            throw new RuntimeException("导出清单到服务器异常" + e.getMessage(), e);
        } finally {
            try {
                workbook.close();
            } catch (Exception e) {
                log.error("导出清单时关闭异常");
                e.printStackTrace();
            }
        }
        return saveFile;
    }

    /**
     * @Method createDatafile
     * @Param
     * @param downloadFileName
     * @Return java.io.File
     * @Description 创建导出文件
     * @Author Wang zhihao
     * @Date 2023/11/1 13:29
     * @Version V1.0
     */
    public File createDatafile(String exportPath, String downloadFileName) throws IOException {
        //获取文件存储在服务器的目录
        // 创建目录
        File myFileExportPath = new File(exportPath);
        if (!myFileExportPath.exists()) {
            myFileExportPath.mkdirs();
        }
        exportPath += downloadFileName;
        byte[] fileNameBytes = exportPath.getBytes("UTF-8");
        // 创建文件
        File myFile = new File(new String(fileNameBytes, "UTF-8"));
        // 判断文件是否存在，如不存在则调用createNewFile()创建新目录，否则跳至异常处理代码
        if (!myFile.exists()) {
            myFile.createNewFile();
        }
        return myFile;
    }

    /**
     * @Method getDownloadScanResultFileData
     * @Param
     * @param scriptPackScanResultEntities
     * @Return java.util.Map<java.lang.Integer,java.util.List<java.util.Map<java.lang.String,java.lang.Object>>>
     * @Description 获取导出数据
     * @Author Wang zhihao
     * @Date 2023/11/1 13:29
     * @Version V1.0
     */
    private Map<Integer, List<Map<String, Object>>> getDownloadScanResultFileData(List<ScriptPackScanResultEntity> scriptPackScanResultEntities) {
        final Map<Integer, List<Map<String, Object>>> sheetMap = new HashMap<>();
        Map<Integer, List<ScriptPackScanResultEntity>> integerListMap = scriptPackScanResultEntities.stream()
                .collect(Collectors.groupingBy(ScriptPackScanResultEntity::getScanSqlIndexAsInt));
        // 空扫描结果处理
        dealEmptyScanResult(integerListMap, sheetMap);
        int flag = 0;
        for (Integer key : integerListMap.keySet()) {
            List<ScriptPackScanResultEntity> scanResultEntities = integerListMap.get(key);
            // 防止单段SQL超过Excel单元格可存放最大长度
            scanResultEntities.stream().peek(s -> {
                if (s.getScanSql() != null && s.getScanSql().length() > 32767) {
                    s.setScanSql(s.getScanSql().substring(0, 32767));
                }
            }).collect(Collectors.toList());
            // 统计扫描状态
            String state = statisticalState(scanResultEntities);
            List<Map<String, Object>> dataMapList = new ArrayList<>();
            Map<String, Object> dataMap = JSON.parseObject(JSON.toJSONString(scanResultEntities.get(0)), Map.class);
            dataMap.put("tableFileds", scanResultEntities.stream().map(t -> JSON.parseObject(JSON.toJSONString(t), Map.class)).collect(Collectors.toList()));
            dataMap.put("sheetName", key == 0 ? StrUtil.format("环境({})", state) : StrUtil.format("{}({})",key, state));
            dataMapList.add(dataMap);
            sheetMap.put(flag, dataMapList);
            flag++;
        }
        return sheetMap;
    }

    /**
     * @Method getDownloadScanListFileData
     * @Param
     * @param scriptPackScanListEntities
     * @Return java.util.Map<java.lang.Integer,java.util.List<java.util.Map<java.lang.String,java.lang.Object>>>
     * @Description 构建汇总导出数据
     * @Author Wang zhihao
     * @Date 2023/12/25 18:25
     * @Version V1.0
     */
    private Map<Integer, List<Map<String, Object>>> getDownloadScanListFileData(List<ScriptPackScanListEntity> scriptPackScanListEntities) {
        final Map<Integer, List<Map<String, Object>>> sheetMap = new HashMap<>();
        Map<String, Object> dataMap = new HashMap<>();
        List<String> successList = scriptPackScanListEntities.stream().filter(s -> StrUtil.equalsIgnoreCase(s.getScanState(), "SUCCESS")).map(ScriptPackScanListEntity::getFileNameWithPath).collect(Collectors.toList());
        List<String> failList = scriptPackScanListEntities.stream().filter(s -> StrUtil.equalsIgnoreCase(s.getScanState(), "FAIL")).map(ScriptPackScanListEntity::getFileNameWithPath).collect(Collectors.toList());
        List<String> warningList = scriptPackScanListEntities.stream().filter(s -> StrUtil.equalsIgnoreCase(s.getScanState(), "WARNING")).map(ScriptPackScanListEntity::getFileNameWithPath).collect(Collectors.toList());
        List<String> ignoreList = scriptPackScanListEntities.stream().filter(s -> StrUtil.equalsIgnoreCase(s.getScanState(), "IGNORE")).map(ScriptPackScanListEntity::getFileNameWithPath).collect(Collectors.toList());
        // 数量统计
        dataMap.put("successNum", successList.size());
        dataMap.put("failNum", failList.size());
        dataMap.put("warningNum", warningList.size());
        dataMap.put("ignoreNum", ignoreList.size());
        // 组合多个清单
        List<SummaryScanResultEntity> summaryScanResult = combineLists(successList, failList, warningList, ignoreList);
        dataMap.put("summaryList", summaryScanResult.stream().map(t -> JSON.parseObject(JSON.toJSONString(t), Map.class)).collect(Collectors.toList()));
        // sheet名
        dataMap.put("sheetName", "Sheet");
        sheetMap.put(0, Arrays.asList(dataMap));
        return sheetMap;
    }

    /**
     * @Method combineLists
     * @Param
     * @param successList
     * @param failList
     * @param warningList
     * @param ignoreList
     * @Return java.util.List<com.hex.ds.code.detection.script.pack.entity.SummaryScanResultEntity>
     * @Description 组合多个List为一个对象
     * @Author Wang zhihao
     * @Date 2023/12/25 19:56
     * @Version V1.0
     */
    private List<SummaryScanResultEntity> combineLists(List<String> successList, List<String> failList, List<String> warningList, List<String> ignoreList) {
        int maxLength = Math.max(Math.max(Math.max(successList.size(), failList.size()), warningList.size()), ignoreList.size());
        return IntStream.range(0, maxLength)
                .mapToObj(i -> new SummaryScanResultEntity(
                        getValueAtIndex(successList, i),
                        getValueAtIndex(failList, i),
                        getValueAtIndex(warningList, i),
                        getValueAtIndex(ignoreList, i)
                ))
                .collect(Collectors.toList());
    }

    private static String getValueAtIndex(List<String> list, int index) {
        return (index < list.size()) ? list.get(index) : null;
    }

    /**
     * @Method dealEmptyScanResult
     * @Param
     * @param integerListMap
     * @param sheetMap
     * @Return void
     * @Description 若指定文件为空扫描结果，处理为空的Excel
     * @Author Wang zhihao
     * @Date 2023/12/18 14:20
     * @Version V1.0
     */
    private void dealEmptyScanResult(Map<Integer, List<ScriptPackScanResultEntity>> integerListMap, Map<Integer, List<Map<String, Object>>> sheetMap) {
        if (integerListMap.isEmpty()) {
            List<Map<String, Object>> dataMapList = new ArrayList<>();
            Map<String, Object> dataMap = new HashMap<>();
            dataMap.put("tableFileds", null);
            dataMap.put("sheetName", 0);
            dataMapList.add(dataMap);
            sheetMap.put(0, dataMapList);
        }
    }

    /**
     * @Method statisticalState
     * @Param
     * @param scanResultEntities
     * @Return java.lang.String
     * @Description 统计扫描状态
     * @Author Wang zhihao
     * @Date 2023/12/11 17:43
     * @Version V1.0
     */
    private String statisticalState(List<ScriptPackScanResultEntity> scanResultEntities) {
        boolean hasFail = scanResultEntities.stream().map(s -> s.getScanState()).anyMatch(state -> "fail".equals(state) || "antlrFail".equals(state) || "badSql".equals(state) ||  "error".equals(state));
        boolean hasWarn = scanResultEntities.stream().map(s -> s.getScanState()).anyMatch(state -> "warning".equals(state) || "ignore".equals(state));
        if (hasFail) {
            return "异常";
        } else if (hasWarn) {
            return "告警";
        } else {
            return "成功";
        }
    }

    /**
     * @Method writeDownloadFileData
     * @Param
     * @param saveFile
     * @param workbook
     * @Return void
     * @Description 写入数据
     * @Author Wang zhihao
     * @Date 2023/11/1 13:29
     * @Version V1.0
     */
    private void writeDownloadFileData(File saveFile, Workbook workbook) throws IOException {
        final FileOutputStream outputStream = new FileOutputStream(saveFile);
        workbook.write(outputStream);
        workbook.close();
        outputStream.close();
    }

}
